
/*
use ManLab
go
*/
--create new Staff
go
create proc Staff_registration
    @fName varchar(50),
	@lName varchar(50),
	@password varchar(50),
	@doj datetime,
	@address varchar(50),
	@phone int,
	@gender bit,
	@dob datetime,
	@eduQual varchar(50),
	@techSkill varchar(50)
as
insert into StaffDetail values(@fName,@lName,@password,@doj,@address,@phone,@gender,@dob,@eduQual,@techSkill)
   
--update Staff
go
create proc Staff_update
    @username varchar(50),
    @fName varchar(50),
	@lName varchar(50),
	@password varchar(50),
	@doj datetime,
	@address varchar(50),
	@phone int,
	@gender bit,
	@dob datetime,
	@eduQual varchar(50),
	@techSkill varchar(50)
as
update StaffDetail set staffFName=@fName,staffLName=@lName,staffPassWord=@password,staffDOJ=@doj,staffAddress=@address,staffPhone=@phone,staffGender=@gender,staffDOB=@dob,staffEduQual=@eduQual,stafftechSkill=@techSkill
                   where staffUserName=@username

--delete Staff
go
create proc Staff_delete
  @username varchar(50)
as
delete from StaffDetail where staffUserName=@username

--check login
go
create proc Check_login
@username varchar(50),
@password varchar(50)
as
select staffID,staffFName,staffPassWord from StaffDetail where staffUserName=@username and staffPassWord=@password

--search Staff
go
create proc Staff_search
@lname varchar(50)
as
select * from StaffDetail where staffLName like ''+@lname+'%'


--create new Patient
go
create proc Patient_registration
    @fName varchar(50),
	@lname varchar(50),
	@address varchar(50),
	@phone int,
	@gender bit,
	@dob datetime
as
insert into PatientDetail values(@fName,@lname,@address,@phone,@gender,@dob)

--update Patient
go
create proc Patient_update
    @id int,
    @fName varchar(50),
	@lName varchar(50),
	@address varchar(50),
	@phone int,
	@gender bit,
	@dob datetime
as
update PatientDetail set pFName=@fName,pLName=@lName,pAddress=@address,pPhone=@phone,pGender=@gender,pDOB=@dob
                   where pID=@id


--search Patient
go
create proc Patient_search
@lname varchar(50)
as
select * from PatientDetail where pLName like ''+@lname+'%'

--create new Test
go
create proc Test_create
	@tname varchar(50),
	@tcost int
as
insert into Test values(@tname,@tcost)

--update Test
go 
create proc Test_update
	@tname varchar(50),
	@tcost int
as
update Test set testName=@tname,testcost=@tcost

--delete  Test
go
create proc Test_delete
    @tid int
as
delete from Test where testID=@tid




/*
alter proc test7 @idvalue int, @tname varchar(50), @colname varchar(50)
as
	declare @existed varchar(50)

	declare @sql varchar(500)
	--select =count(*) from  where @id=@idvalue
	set @sql= 'select '+convert(varchar(10),@existed)+'=count (*) from '+@tname+' where '+@colname+'='+convert(varchar(10),@idvalue)
     exec (@sql)

print @existed
if @existed='0'
begin
 print 'true'
end
else 
begin 
print 'false'

end

exec test7 1,'Report','testID'

*/
--Procedure check exist
create proc Validate_exists
   @idvalue int,
   @tname varchar(50),
   @colname varchar(50)
as
declare @existed int
declare @sql varchar(200)
create table #number(
number int
)
set @sql= 'insert into #number (number) select '+@colname+' from '+@tname+' where '+@colname+'='+convert(varchar(10),@idvalue)
exec (@sql)
select @existed=count(*) from #number
return @existed


exec Validate_exists 1,'Report','testID'

-- Trigger insert Billing
alter trigger TG_InsertBilling
on Billing
instead of insert
as
declare @pID int 
declare @date datetime
declare @amount int
select @date=bDate,@amount=bAmount,@pID=pID from inserted
declare @rValue int
exec @rValue=Validate_exists @pID,'PatientDetail','pID'
if @rValue!=0
begin
insert into Billing select @date,@amount,@pid  from inserted
end

--Trigger insert BillDetail
alter trigger TG_InsertBillDetail
on BillDetail
instead of insert
as
declare @bid int 
declare @testid int 
declare	@testcost int
select @bid=bID,@testid=testID,@testcost=testcost from inserted
declare @value int
declare @value1 int
exec @value=Validate_exists @bid,'Billing','bID'
exec @value1=Validate_exists @testid,'Test','testID'
if @value!=0 and @value1!=0
begin
 insert into BillDetail select @bid,@testid,@testcost from inserted
end

--Trigger insert ItemRequire
create trigger TG_InsertItemRequire
on ItemRequire
instead of insert
as
declare @testid int 
declare @sid int
declare @inorequire int
select @testid=testID,@sid=[sID],@inorequire=iNoRequire from inserted
declare @value2 int
declare @value3 int
exec @value2=Validate_exists @sid,'StockDetail','[sID]'
exec @value3=Validate_exists @testid,'Test','testID'
if @value2!=0 and @value3!=0
begin
 insert into ItemRequire select @testid,@sid,@inorequire from inserted
end

--Trigger insert Report
alter trigger TG_InsertReport
on Report
instead of insert
as
declare @testid int 
declare @pid int
declare @staffid int
declare @testdate datetime
declare @report varchar(50)
declare @status bit
declare @did int
select @did=dID,@testid=testID,@pid=pID,@staffid=staffID,@testdate=testDate,@report=report,@status=[status] from inserted
declare @value4 int
declare @value5 int
declare @value6 int
declare @value7 int
exec @value4=Validate_exists @did,'DoctorDetail','dID'
exec @value5=Validate_exists @testid,'Test','testID'
exec @value6=Validate_exists @pid,'PatientDetail','pID'
exec @value7=Validate_exists @staffid,'StaffDetail','staffID'
if @value4!=0 and @value5!=0 and @value6!=0 and @value7!=0
begin
insert into Report select @did,@testid,@pid,@staffid,@testdate,@report,@status from inserted
end

----Trigger Update Billing 
alter trigger TG_UpdateBilling
on Billing
instead of update
as
if(update(pID))
begin
declare @pid int
declare @pid1 int
declare @value4 int
select @pid=pID from inserted
select @pid1=pID from deleted
exec @value4=Validate_exists @pid,'PatientDetail','pID'
if @value4!=0
update Billing set pID=@pid where pID=@pid1
end

----Trigger Update BillingDetail 
alter trigger TG_UpdateBillDetail
on Billing
instead of update
as
if(update(pID))
begin
declare @bid int
declare @bid1 int
declare @value4 int
select @bid=bID from inserted
select @bid1=bID from deleted
exec @value4=Validate_exists @bid,'Billing','bID'
if @value4!=0
update BillDetail set bID=@bid where bID=@bid1
end

if(update(testID))
begin
declare @testid int
declare @testid1 int
declare @value4 int
select @testid=testID from inserted
select @testid1=testID from deleted
exec @value4=Validate_exists @testid,'Test','testID'
if @value4!=0
update BillDetail set testID=@testid where testID=@testid1
end

----Trigger Update ItemRequire 
alter trigger TG_UpdateItemRequire
on ItemRequire
instead of update
as
if(update([sID]))
begin
declare @sid int
declare @sid1 int
declare @value4 int
select @sid=[sID] from inserted
select @bid1=[sID] from deleted
exec @value4=Validate_exists @bid,'Billing','[sID]'
if @value4!=0
update ItemRequire set [sID]=@sid where [sID]=@sid1
end

if(update(testID))
begin
declare @testid int
declare @testid1 int
declare @value4 int
select @testid=testID from inserted
select @testid1=testID from deleted
exec @value4=Validate_exists @testid,'Test','testID'
if @value4!=0
update ItemRequire set testID=@testid where testID=@testid1
end


--Trigger update Report
alter trigger TG_UpdateReport
on Report
instead of update
as
if(update(dID))
begin
declare @did int
declare @did1 int
declare @value4 int
select @did=dID from inserted
select @did1=dID from deleted
exec @value4=Validate_exists @did,'DoctorDetail','dID'
if @value4!=0
update Report set dID=@did where  dID=@did1
end

if(update(testID))
begin
declare @testid int
declare @testid1 int
declare @value5 int
select @testid=testID from inserted
select @testid1=testID from deleted
exec @value5=Validate_exists @testid,'Test','testID'
if @value5!=0
update Report set testID=@testid where  testID=@testid1
end

if(update(pID))
begin
declare @pid int
declare @pid1 int
declare @value6 int
select @pid=pID from inserted
select @pid1=pID from deleted
exec @value6=Validate_exists @pid,'PatientDetail','pID'
if @value6!=0
update Report set pID=@pid where  pID=@pid1
end

if(update(staffID))
begin
declare @staffid int
declare @staffid1 int
declare @value7 int
select @staffid=staffID from inserted
select @staffid1=staffID from inserted
exec @value7=Validate_exists @staffid,'StaffDetail','staffID'
if @value7!=0
update Report set staffID=@staffid where  staffID=@staffid1
end

/*--Trigger delete Report
alter trigger TG_DeleteReport
on Report
for delete
as
declare @rpid int
if @rpid not in(select reportID from deleted)
begin
print 'Ket qua khong hop le 123'
--rollback
end
*/

/*
if Object_ID('ManLab.dbo.ssfsdf','U') is not null
print 'exists'
else
print 'not exists'
select * from Billing
select * from PatientDetail
insert into Billing values('2012/01/01',123,2)
insert into Report values(2,123,2,1,'2012/01/01','w',1)
insert into ItemRequire values(1,123,2)
insert into BillDetail values(111,1,11) 
update Report set dID=1 where dID=2
delete from Report where reportID=1
*/

alter proc CreateStaffUsername
  @fName varchar(50),
  @lName varchar(50)
as
declare @kytu varchar(50)
declare @kytu1 varchar(50)
declare @kytu2 varchar(50)
declare @kytu3 varchar(50)
declare @username varchar(50)
begin
set @kytu3=upper(substring(@fName,1,1))+lower(substring(@fName,2,len(@fName)))
--set @kytu = upper(substring(@lName,1,1))
set @kytu=charindex(' ', @lName,1)
set @kytu1 = upper(substring(substring(@lName,charindex(' ', @lName,1)+1,len(@lName)),1,1))
set @kytu2=@kytu+@kytu1
set @username=@kytu3+@kytu2
end
print @kytu
print @kytu1
print @kytu2
print @kytu3
print @username

exec CreateStaffUsername 'quan','nguyen duc anh'


alter proc CreateStaffUsername1
 
@document varchar(1000)
as

declare @document2 varchar(1000) 
declare @document1 varchar(1000) 
declare @document3 varchar(1000) 
set @document2=''
declare @found varchar(2)
declare	@nstart int
set @nstart	= 1
set @document1=substring(@document,1,1)
while charindex(' ', @document, @nstart) > 0
begin

set @document2 =@document2+ substring(@document,CHARINDEX(' ', @document,@nstart)+1,1)
set @nstart = charindex(' ', @document, @nstart) + 1
set @document3=@document1+@document2
end
print @document1
print @document2
print @document3

exec CreateStaffUsername1 'nguyen duc quan hoang'

insert into PatientDetail values('aaa','aaa','bbb','123434',default,'2011/01/01')
insert into PatientDetail values('bbb','bbb','ccc','55555',default,'2111/11/11')
insert into Billing values('2012/01/01',123,11)
update Billing set pID=1 where pID=11


select * from PatientDetail
select * from Billing

delete  from Billing
delete  from PatientDetail